<?php
class Users extends Dao
{
	private static $_instance = null;
	protected $_db = null, $_cache = null;
	const AUTH_WITH_USERNAME = 1,
		  AUTH_WITH_EMAIL = 2,
		  AUTH_ERROR_USERNAME_INVALID = -2, //username not found or invalid
		  AUTH_ERROR_PASSWORD_INVALID = -1, //username found but wrong password
		  AUTH_ERROR_ACCOUNT_LOCKED = 0; //username and password are valid but acocunt is locked
	protected function __construct()
	{
		parent::__construct();
	}
	
	public static function getInstance() 
    {
        if (null === self::$_instance) {
            self::$_instance = new self();
        }
        return self::$_instance;
    }
	
	
	public function getList(array $args = array())
	{
		try{
			$defaults = array(
				'keyword' => null,
				'role_id' => null,
				'created_from' => null,
				'created_to' => null,
				'user_status' => null,
				'includeProfile' => false,
				'page' => 1,
				'limit' => MIN_ROWS_PER_PAGE,
				'order_by' => 'user_id',
				'order' => 'DESC'
			);
			$args = $this->args_merge($defaults, $args, true);
			
			$strCacheKey = $this->generateKeyCache(CACHE_USERS_LIST, $args);
			$arrResults = $this->_cache->get($strCacheKey);
			/*cache missed: get from db*/
			if($arrResults === false)
			{
				$intPage = $args['page'];
				$intLimit = $args['limit'];
				$intStart = ($intPage-1)*$intLimit;
				
				$bindParams = array();
				$arrWhere = array();
				if(!empty($args['keyword'])){
					$arrWhere[] = "(user_username LIKE :keyword OR user_email LIKE :keyword)";
					$bindParams[] = array(':keyword', $args['keyword'], PDO::PARAM_STR);
				}
				if(!empty($args['created_from'])){
					$args['created_from'] = date('Y-m-d', strtotime($args['created_from']));
					$arrWhere[] = "user_created_date >= :created_from";
					$bindParams[] = array(':created_from', $args['created_from'], PDO::PARAM_STR);
				}
				if(!empty($args['created_to'])){
					$args['created_to'] = date('Y-m-d', strtotime($args['created_to']));
					$arrWhere[] = "user_created_date <= :created_to";
					$bindParams[] = array(':created_to', $args['created_to'], PDO::PARAM_STR);
				}
				if($args['user_status'] !== null){
					$arrWhere[] = "user_status = :status";
					$bindParams[] = array(':status', $args['user_status'], PDO::PARAM_STR);
				}

				$strWhere = '';
				if(!empty($arrWhere)){
					$strWhere = 'WHERE '.implode(' AND ', $arrWhere);
				}
				
				$strRoleCond = "";				
				if(!empty($args['role_id'])){
					$strRoleCond = "INNER JOIN tbl_user_role ur ON u.user_id = ur.user_id AND FIND_IN_SET(ur.role_id, :roles)";
					$bindParams[] = array(':roles', $args['role_id'], PDO::PARAM_STR);
				}
				
				$strProfileSelect = "";
				$strProfileCond = "";
				if($args['includeProfile']){
					$strProfileSelect = ", profile_id, profile_firstname, profile_middlename, profile_lastname, profile_displayname, profile_email, profile_gender, profile_birthdate, profile_birthplace, profile_id_card, profile_image, profile_phone, profile_phone_alt, profile_address, profile_address_alt, profile_fax, profile_website, profile_location, profile_country, profile_description, profile_relationship, profile_created_date, profile_last_modified_date, profile_theme, profile_timezone, profile_language, profile_privacy, profile_allow_notify, profile_settings, profile_status";
					$strProfileCond = "INNER JOIN tbl_user_profiles up ON u.user_id = up.user_id";
				}
				
				$strSql = " SELECT SQL_CALC_FOUND_ROWS u.user_id, u.user_username, u.user_email, u.user_fullname, u.user_description, u.user_created_date, u.user_last_modified_date, u.user_last_access, u.user_last_login, u.user_secret_key, u.user_verified, u.user_status, 
								   (SELECT GROUP_CONCAT(ur.role_id) FROM tbl_user_role ur WHERE ur.user_id = u.user_id) AS role_id {$strProfileSelect}
							FROM tbl_users u {$strRoleCond} {$strProfileCond}
							{$strWhere}
							LIMIT :intStart, :intLimit";
							
				$cmd = $this->_db->createCommand($strSql);
				foreach($bindParams as $p){
					$cmd->bindParam($p[0],$p[1],$p[2]);
				}
				$cmd->bindParam(":intStart",$intStart,PDO::PARAM_INT);
				$cmd->bindParam(":intLimit",$intLimit,PDO::PARAM_INT);

				$arrResults = $cmd->queryAll();
				if(!empty($arrResults)){
					$arrResults[0]['total_rows'] = $this->_db->createCommand('SELECT FOUND_ROWS();')->queryScalar();
				}

				/*save to cache*/
				$this->_cache->set($strCacheKey, $arrResults, DEFAULT_LONG_CACHE_EXPIRED);
				$this->setMasterCache($strCacheKey, CACHE_USERS_LIST);
			}
			return $arrResults;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function getByUsername($username){
		if($username === null || $username == '') throw new Exception('Invalid username!');
		try{
			$strCacheKey = $this->generateKeyCache(CACHE_USER, 'USERNAME_'.$username);
			$user = $this->_cache->get($strCacheKey);
			/*cache missed: get from db*/
			if($user === false)
			{
				$strSql = " SELECT u.user_id
							FROM tbl_users u
							WHERE u.user_username=:username";
				$cmd = $this->_db->createCommand($strSql);
				$cmd->bindParam(":username",$username,PDO::PARAM_STR);
				$id = $cmd->queryScalar();
				
				$user = array();
				if(!empty($id)){
					$user = $this->get($id);
				}
				
				/*save to cache*/
				$this->_cache->set($strCacheKey, $user, DEFAULT_LONG_CACHE_EXPIRED);
				$this->setMasterCache($strCacheKey, CACHE_USER.'_'.$id);				
			}
			return $user;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function get($id)
	{
		if(empty($id))throw new Exception('user is null');
		try{
			$strCacheKey = $this->generateKeyCache(CACHE_USER, $id);
			$user = $this->_cache->get($strCacheKey);
			/*cache missed: get from db*/
			if($user === false)
			{
				$strSql = " SELECT u.user_id, u.user_username, u.user_email, u.user_fullname, u.user_description, u.user_created_date, u.user_last_modified_date, u.user_last_access, u.user_last_login, u.user_secret_key, u.user_verified, u.user_status, 
								  (SELECT GROUP_CONCAT(role_id) FROM tbl_user_role WHERE user_id = u.user_id) as roles
							FROM tbl_users u
							WHERE u.user_id=:id";
				$cmd = $this->_db->createCommand($strSql);
				$cmd->bindParam(":id",$id,PDO::PARAM_STR);
				$user = $cmd->queryRow();
				
				if(isset($user['roles'])){
					$user['roles'] = explode(',', $user['roles']);
				}
				
				/*save to cache*/
				$this->_cache->set($strCacheKey, $user, DEFAULT_LONG_CACHE_EXPIRED);
				$this->setMasterCache($strCacheKey, CACHE_USER.'_'.$id);
			}
			return $user;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function getOauthUser($uid, $provider){
		if(empty($uid) || empty($provider)) throw new Exception('uid and provider is null');
		try{
			$strSql = " SELECT u.*
						FROM tbl_users
						WHERE user_id = (
											SELECT user_id
											FROM tbl_oauth_users u
											WHERE u.oauth_uid=:uid and u.oauth_provider=:provider
										);";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":uid",$uid,PDO::PARAM_STR);
			$cmd->bindParam(":provider",$provider,PDO::PARAM_STR);
			return $arrResults = $cmd->queryRow();
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function insert(array $args)
	{		
		try{
			$defaults = array(
				'user_username' => null,
				'user_email' => null,
				'user_fullname' => null,
				'user_password' => null, //raw password
				'user_secret_key' => Utils::getRandomString(5),
				'oauth_uid' => null,
				'oauth_provider' => null
			);
			$args = $this->args_merge($defaults, $args);
			//insert user
			$cmd = $this->_db->createCommand();
			$cmd->insert('tbl_users',array(
				'user_username' => $args['user_username'],
				'user_email' => $args['user_email'],
				'user_fullname' => $args['user_fullname'],
				'user_secret_key' => $args['user_secret_key'],
			));
			$intInsertId = Yii::app()->db->getLastInsertId();
			//oauth user
			if(!empty($args['oauth_provider']) && !empty($args['oauth_uid'])){
				$cmd->insert('tbl_oauth_users',array(
					'user_id' => $intInsertId,
					'oauth_provider' => $args['oauth_provider'],
					'oauth_uid' => $args['oauth_uid']
				));
			}
			//update password
			$this->updatePassword($intInsertId, $args['user_password']);
			
			/*clear cache*/
			if(!empty($intInsertId)){
				$this->clearMasterCache(CACHE_USERS_LIST);	
			}	
			
			return $intInsertId;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function update($id, array $args)
	{		
		try{
			$defaults = array(
				'user_username' => null,
				'user_email' => null,
				'user_fullname' => null,				
			);
			$args = $this->args_merge($defaults, $args);
			//update user
			$cmd = $this->_db->createCommand();
			$cmd->update('tbl_users',$args, 'user_id=:id', array(':id'=>$id));	
			/*clear cache*/
			$this->clearMasterCache(CACHE_USER.'_'.$id);	
			$this->clearMasterCache(CACHE_USERS_LIST);
			return 1;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function updatePassword($id, $password)
	{
		try{
			//gen salt
			$salt = Utils::getRandomString(5);
			//hash password
			$hash = Utils::hashPassword($password.$salt);
			$args = array(
				'user_password' => $hash,	
				'user_salt' => $salt		
			);
			$cmd = $this->_db->createCommand();
			return $cmd->update('tbl_users',$args, 'user_id=:id', array(':id'=>$id));
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function updateSecretKey($id)
	{
		if(empty($id)) throw new Exception('user id is null');
		try{
			$this->_db->createCommand()->update('tbl_users',array('user_secret_key' => Utils::getRandomString(5)), 'user_id=:id', array(':id'=>$id));
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function authenticate($username, $password, $type = self::AUTH_WITH_USERNAME){
		try{
			if($username == '') return self::AUTH_ERROR_USERNAME_INVALID;
			//get user info
			if($type == self::AUTH_WITH_USERNAME)
				$strSql = "SELECT user_id, user_password, user_salt, user_status FROM tbl_users WHERE user_username=:username";
			else
				$strSql = "SELECT user_id, user_password, user_salt, user_status FROM tbl_users WHERE user_email=:username";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":username",$username,PDO::PARAM_STR);
			$user = $cmd->queryRow();
			//check if user exists?
			if(empty($user)){		
				return self::AUTH_ERROR_USERNAME_INVALID;
			}
			//check if user is locked?
			if(empty($user['user_status'])){
				return self::AUTH_ERROR_ACCOUNT_LOCKED;	
			}
			//check if credentials is valid?
			if(!Utils::verifyPassword($password.$user['user_salt'], $user['user_password'])){
				return self::AUTH_ERROR_PASSWORD_INVALID;
			}
			//authenticated ok
			return $user['user_id'];
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function validatePassword($id, $password){
		try{
			if(empty($id)) return false;
			
			$strSql = " SELECT user_password, user_salt
						FROM tbl_users u
						WHERE user_id=:id";
			$cmd = $this->_db->createCommand($strSql);
			$cmd->bindParam(":id",$id,PDO::PARAM_STR);
			$arrResults = $cmd->queryRow();
			
			if(!empty($arrResults)){
				if($arrResults['user_password'] == md5($password.$arrResults['user_salt'])) return true;
			}
			return false;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function delete($id){
		try{
			$cmd = $this->_db->createCommand();
			//delete user permissions
			$cmd->delete('tbl_user_permission', 'user_id=:id', array(':id'=>$id));
			//delete user from roles
			$cmd->delete('tbl_user_role', 'user_id=:id', array(':id'=>$id));
			//delete feedback
			$cmd->delete('tbl_user_feedback', 'feedback_creator_id=:id', array(':id'=>$id));
			//delete profile
			$cmd->delete('tbl_user_profiles', 'user_id=:id', array(':id'=>$id));
			//delete user
			$cmd = $this->_db->createCommand();
			$intResult = $cmd->delete('tbl_users', 'user_id=:id', array(':id'=>$id));		
			/*clear cache*/
			if(!empty($intResult)){
				$this->clearMasterCache(CACHE_USER.'_'.$id);	
				$this->clearMasterCache(CACHE_USERS_LIST);
			}
			return $intResult;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function getToken($user)
	{
		/* generate token */
		$time = time();
		if(is_array($user) && !empty($user['user_id']) && !empty($user['user_username']) && !empty($user['user_email']) && !empty($user['user_secret_key']))
			return base64_encode( $user['user_id'].'::'.md5($user['user_id'].'::'.$user['user_username'].'::'.$user['user_email'].'::'.$user['user_secret_key'].'::'.$time).'::'.$time);	
		return '';
	}
	
	public function validateToken($strToken, $expiration = DEFAULT_USER_TOKEN_EXPIRATION, &$decoded = array())
	{
		if(!empty($strToken)){
			$token = explode('::', base64_decode($strToken));
			/*check if token is valid*/
			if(count($token) == 3){
				
				//token valid -> get data from token
				$key = $token[0];
				$hash = $token[1];
				$time = $token[2];
				
				//get user info
				$user = Users::getInstance()->get($key);
				
				/*check if hash is valid*/
				if(!empty($user) && $hash == md5($user['user_id'].'::'.$user['user_username'].'::'.$user['user_email'].'::'.$user['user_secret_key'].'::'.$time)){
					/* check token expiration */
					if(time() <= $time+$expiration){
						$decoded['key']   		= $token[0];
						$decoded['hash'] 		= $token[1];
						$decoded['lifetime']   = $token[2];
						$decoded['object'] 	= $user;
						return USER_TOKEN_OK;
					}
					return USER_TOKEN_EXPIRED;
				}
			}
		}
		return USER_TOKEN_INVALID;
	}
	
	public function getProfile($userId)
	{
		if($userId === null || $userId == '') throw new Exception('Invalid Param!');
		try{
			$strCacheKey = $this->generateKeyCache(CACHE_USER, 'PROFILE_'.$userId);
			$profile = $this->_cache->get($strCacheKey);
			/*cache missed: get from db*/
			if($profile === false)
			{
				$strSql = " SELECT *
							FROM tbl_user_profiles
							WHERE user_id=:uid ";
				$cmd = $this->_db->createCommand($strSql);
				$cmd->bindParam(":uid",$userId,PDO::PARAM_STR);
				$profile = $cmd->queryRow();
				
				/*save to cache*/
				$this->_cache->set($strCacheKey, $profile, DEFAULT_LONG_CACHE_EXPIRED);
				$this->setMasterCache($strCacheKey, CACHE_USER.'_'.$userId);
			}
			return $profile;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}
	
	public function insertProfile($userId, array $args){
		if(empty($userId)) throw Exception('Invalid Input');
		$defaults = array(
			'profile_firstname' => null,
			'profile_middlename' => null,
			'profile_lastname' => null,
			'profile_email' => null,
			'profile_gender' => null,
			'profile_birthdate' => null,
			'profile_birthplace' => null,
			'profile_id_card' => null,
			'profile_image' => null,
			'profile_phone' => null,
			'profile_phone_alt' => null,
			'profile_address' => null,
			'profile_address_alt' => null,
			'profile_fax' => null,
			'profile_website' => null,
			'profile_location' => null,
			'profile_country' => null,
			'profile_description' => null,
			'profile_relationship' => null,
			'profile_created_date' => new CDbExpression('NOW()'),
			'profile_theme' => null,
			'profile_timezone' => null,
			'profile_language' => null,
			'profile_privacy' => null,
			'profile_allow_notify' => 1,
			'profile_settings' => null,
			'profile_status' => 1
		);
		$args = $this->args_merge($defaults, $args);
		$args['user_id'] = $userId;
		try{
			$cmd = $this->_db->createCommand();
			$cmd->insert('tbl_user_profiles',$args);
			return Yii::app()->db->getLastInsertId();
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}
	}

	public function updateProfile($userId, array $args)
	{
		if(empty($userId)) throw Exception('Invalid Input');
		$defaults = array(
			'profile_firstname' => null,
			'profile_middlename' => null,
			'profile_lastname' => null,
			'profile_email' => null,
			'profile_gender' => null,
			'profile_birthdate' => null,
			'profile_birthplace' => null,
			'profile_id_card' => null,
			'profile_image' => null,
			'profile_phone' => null,
			'profile_phone_alt' => null,
			'profile_address' => null,
			'profile_address_alt' => null,
			'profile_fax' => null,
			'profile_website' => null,
			'profile_location' => null,
			'profile_country' => null,
			'profile_description' => null,
			'profile_relationship' => null,
			'profile_last_modified_date' => new CDbExpression('NOW()'),
			'profile_theme' => null,
			'profile_timezone' => null,
			'profile_language' => null,
			'profile_privacy' => null,
			'profile_allow_notify' => null,
			'profile_settings' => null,
			'profile_status' => 1
		);
		$args = $this->args_merge($defaults, $args);
		$args['user_id'] = $userId;
		try{
			$cmd = $this->_db->createCommand();
			$intResult = $cmd->update('tbl_user_profiles',$args,'user_id=:uid', array(':uid'=>$userId));
			/*clear cache*/
			if(!empty($intResult)){
				$this->clearMasterCache(CACHE_USER.'_'.$userId);	
			}
			return $intResult;
		} catch(Exception $ex){
			throw new Exception($ex->getMessage());
		}	
	}
}